package com.xrit.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;

/**
 * 类名称: ExcelDemo
 * 类描述:TODO
 * 创建人: czb
 * 创建时间:2022/6/20 下午 03:06
 * Version 1.0.0
 */
public class ExcelDemo {
    //读取磁盘Excel的文件 后缀是xlsx
    @Test
    public void readFromExcel() throws IOException {
        InputStream inputStream = new FileInputStream("D:\\read.xlsx");
        //创建Workbook的实现类
        Workbook workbook = new XSSFWorkbook(inputStream);
        //通过Workbook对象获取到工作表
        Sheet sheet = workbook.getSheetAt(0);
        //通过工作表获取行 ，列 ，单元格
        for (Row row : sheet) {
            //通过行拿列
            for (Cell cell : row) {
                //根据当前列的第一个拿单元格
                int cellType = cell.getCellType();
                if (Cell.CELL_TYPE_NUMERIC == cellType) {
                    //输出 数值类型的单元格
                    System.out.print(cell.getNumericCellValue() + " ");
                } else {
                    //输出 从单元格取值
                    System.out.print(cell.getStringCellValue() + " ");
                }
            }
            System.out.println();
        }
        //关闭工作表
        inputStream.close();
        workbook.close();
    }

    //导出excel，获取最后一行
    @Test
    public void exportExcel_lastRow() throws IOException {
        InputStream inputStream = new FileInputStream("D:\\read.xlsx");
        //创建Workbook的实现类
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //获取第一个工作表
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //获取最后的行号
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                String stringCellValue = row.getCell(j).getStringCellValue();
                System.out.print(stringCellValue + " ");
            }
            System.out.println();
        }
        //关闭工作表
        inputStream.close();
        workbook.close();
    }

    /*往里写入数据*/
    @Test
    public void createExcel() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("测试写Excel");
        Row row = sheet.createRow(0);
        //表头
        row.createCell(0).setCellValue("姓名");
        row.createCell(1).setCellValue("年龄");
        row.createCell(2).setCellValue("所在地");

        row=sheet.createRow(1);
        row.createCell(0).setCellValue("张三");
        row.createCell(1).setCellValue(18);
        row.createCell(2).setCellValue("长沙");

        row=sheet.createRow(2);
        row.createCell(0).setCellValue("李四");
        row.createCell(1).setCellValue(20);
        row.createCell(2).setCellValue("长春");

        //保存工作簿，持久化在磁盘里、
        workbook.write(new FileOutputStream(new File("D:\\createExcel.xlsx")));
        workbook.close();
    }
}
